﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.GERP.GHRMS.MODEL;
using System.Configuration;
using System.Data;
using KPIS.DBM;

namespace KPIS.GERP.GHRMS.DAL
{
    public class TR_TrainingRequestDAL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

        public IList<TR_TrainingRequestInfo>GetFill()
        //IList<TR_TrainingRequestInfo>GetFill(TR_TrainingRequestInfo infoparam)
            //(string training_status_descr, DateTime date_training, string trainig_cur_descr, string training_place, string training_create)
        {
            //เช็คว่า Data Table มีข้อมูลหรือไม่
            DataRowCollection drc = null;
            IList<TR_TrainingRequestInfo> infoList = new List<TR_TrainingRequestInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                //dbManager.AddParameters(1, "@date_training", infoparam.date_training);
                //dbManager.AddParameters(2, "@training_cur_descr", infoparam.training_cur_descr);
                //dbManager.AddParameters(3, "@training_place", infoparam.training_place);
                //dbManager.AddParameters(4, "@training_create", infoparam.training_create);


                string strSQL = "SELECT tr.training_cur_descr, tr.requested_when"
                                + " ,tr.training_place,tr.training_create"
                                + " ,tr.date_training,pe.first_name, pe.last_name"
                                + " ,tr.remarks,js.job_site_name"
                                + " ,tr.request_seq,pe.emp_seq"
                                + " ,mp.pos_seq,js.job_site_seq"
                                + " ,ts.training_status_descr,tr.status_seq"
                                + " FROM hrm_training_request tr"
                                + " Left Join hrm_ps_employees pe ON tr.emp_seq = pe.emp_seq"
                                + " Left Join hrm_mp_positions mp ON pe.pos_seq = mp.pos_seq"
                                + " Left Join hrm_mp_job_sites js ON mp.job_site_seq = js.job_site_seq"
                                + " Left Join hrm_training_status ts ON ts.status_seq = tr.status_seq"
                                + " WHERE tr.record_status not like @delete_status";
                                //+ " AND tr.status_seq not like 1" ;
                                //+ " WHERE hrm_training_status.training_status_descr = @training_status_descr";
                                //+ (date_training.Length > 0 ? " AND tr.training_cur_descr LIKE @date_training" : "")
                                //+ (trainig_cur_descr.Length > 0 ? " AND tr.date_training = @date_training" : "")
                                //+ (training_place.Length > 0 ? " AND tr.training_place LIKE @training_place" : "")
                                //+ (training_create.Length > 0 ? " AND tr.training_create LIKE @training_create" : "");
                              

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                
                if (drc.Count > 0)
                {
                    int i = 0;

                    while (i < drc.Count)
                    {                        
                        TR_TrainingRequestInfo info = new TR_TrainingRequestInfo();
                        info.request_seq = Convert.ToInt32(drc[i]["request_seq"]);//1
                        info.training_cur_descr = drc[i]["training_cur_descr"].ToString();//2
                        info.requested_when = Convert.ToDateTime(drc[i]["requested_when"]);//3
                        info.training_place = drc[i]["training_place"].ToString();//4
                        info.training_create = drc[i]["training_create"].ToString();//5
                        info.remarks = drc[i]["remarks"].ToString();//6
                        info.date_training = Convert.ToDateTime(drc[i]["date_training"]);//7
                        //info.date_training = drc[i]["date_training"].ToString();
                        info.status_seq = cn.ConvertNullInt(drc[i]["status_seq"]);//8                    
                        info.training_status_descr = drc[i]["training_status_descr"].ToString();//9
                        info.emp_seq = cn.ConvertNullInt(drc[i]["emp_seq"]);//10
                        info.emp_name = drc[i]["first_name"].ToString()+" "+ drc[i]["last_name"].ToString();//12
                        info.pos_seq = cn.ConvertNullInt(drc[i]["pos_seq"]);//11
                        info.job_site_seq = cn.ConvertNullInt(drc[i]["job_site_seq"]);//13
                        info.job_site_name = drc[i]["job_site_name"].ToString();//14
                        
                        //info.job_site_name = drc[i]["job_site_name"].ToString();//14
                        infoList.Add(info);

                        i++;
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose(); //สั้งเคลียร์ Connections ของ Method
            }
            return infoList;
        }

        public void UpdateStatus(TR_TrainingRequestInfo info)
            //(Nullable<Int32> request_seq, string updated_by, DateTime updated_when)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@request_seq", info.request_seq);
                dbManager.AddParameters(1, "@updated_by", info.updated_by);
                dbManager.AddParameters(2, "@updated_when", info.updated_when);
                dbManager.AddParameters(3, "@status_seq", info.status_seq);

                strSQL = "UPDATE hrm_training_request tr"
                        + " SET tr.status_seq = @status_seq"
                        + ", tr.updated_by = @updated_by"
                        + ", updated_when = @updated_when"
                        + " WHERE tr.request_seq = @request_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataSet GetData(string status_seq)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@status_seq", cn.ConvertNullInt(status_seq));

            string sql = "SELECT hrm_training_status.status_seq AS Sequence ,hrm_training_status.training_status_descr AS Name"
                        + " FROM hrm_training_status"
                        + (status_seq != "" ? " WHERE hrm_training_status.status_seq = @status_seq" : "");
            
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;

        }
    }
}
